*Monthly employment and wages by supersector
cd "C:\Users\MurphyD\Documents\BLSemployment\County employment"
clear
capture erase "county industry employmentIND.dta"
*To run the commented out code, first download the singlefiles from the QCEW**********
/*

forvalues yr = 2002 (1) 2013 {
  insheet using `yr'q1_q4singlefile.csv, clear
  keep if agglvl_code==74 | agglvl_code==75 | (industry=="10" & own==0)
  generate State = 1 if own_code== 1 | own_code==2 | own_code==3
    replace State = 0 if own_code==5
  destring industry_code, generate (industry) force
  keep if (industry>=10 & industry<1000) | industry==.
    generate Ind = 1 if industry==11
    replace Ind = 2 if industry==21
    replace Ind = 3 if industry==22
    replace Ind = 4 if industry==23
    replace Ind = 5 if industry>=310 & industry<320
    replace Ind = 6 if industry>=320 & industry<330
    replace Ind = 7 if industry>=330 & industry<340
    replace Ind = 8 if industry==42
    replace Ind = 9 if industry_code=="44-45"
    replace Ind = 10 if industry_code=="48-49"
    replace Ind = 11 if industry==51
    replace Ind = 12 if industry==52 | industry==53
    replace Ind = 13 if industry==54 | industry==55
    replace Ind = 14 if industry==56
    replace Ind = 15 if industry==61
    replace Ind = 16 if industry==62
    replace Ind = 17 if industry==71
    replace Ind = 18 if industry==72
    replace Ind = 19 if industry==81
    replace Ind = 20 if State==1
    replace Ind=0    if industry==10
    collapse (sum) month1_emplvl month2_emplvl month3_emplvl total_qtrly_wages taxable_qtrly_wages (mean) avg_wkly_wage, by( area_fips Ind year qtr)
  
  drop if Ind==. 

  saveold county_employmentIND`yr'.dta, replace
  
   capture append using "county industry employmentIND.dta"
  describe, short
  save "county industry employmentIND.dta", replace
     
 }
 */
  use "county industry employmentIND.dta", clear
rename Ind ind
destring area_fips, replace force
rename area_fips fips
rename qtr quarter

*rename month3_emp emp
egen emp=rowmean(month1_emp month2_emp month3_emp)
rename avg_wkly wage
rename total_qtrly_wages inc

sort fips ind year quarter

save tempInd, replace
  ***will have emp and wage by industry, ownership code
drop if emp==0
drop if wage==0
  *drop month1 and 2 employment
drop month* taxable
reshape wide emp wage inc, i(fips year quarter ) j(ind)


destring fips, replace force
drop if fips==.
save CountyEmpInd, replace

*county annual
collapse (mean) emp* (sum) wage* inc*, by(fips year)

*cbsa annual
merge m:1 fips using countyCBSAmap.dta
keep if cbsa<.
collapse (sum) emp* inc*, by(cbsa year)
save CBSAempAnn.dta, replace


